use master
go

create database Student
on
(
	name='Student',
	filename='E:\Student.mdf',
	size=10mb,
	maxsize=50mb,
	filegrowth=20%
)
log on
(
	name='Student_log',
	filename='E:\Student.mdf',
	size=10mb,
	maxsize=50mb,
	filegrowth=20%
)
go

use Student
go

create table Class
(
	ClassID int primary key  identity(1,1),
	ClassName nvarchar(20) unique not null
)

create table Student
(
	StuID int  primary key identity(1,1),
	ClassID int foreign key references Class(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) Check(StuSex in('男','女')),
	StuBirthday date ,
	StuPhone nvarchar(11) unique

)

create table Course
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int not null default(1) check(CourseCredit>=1 and CourseCredit<=5)
)

create table Score
(
	ScoreID int identity(1,1),
	StuID int  ,
	CourseID int ,
	Score decimal(5,2) unique not null
)

alter table Student add StuAddress nvarchar(200)

alter table Score add   constraint FK_Score_Sccore primary key(ScoreID) 

alter table Score add  constraint FK_Score_StuID  foreign key(StuID)  references Student (StuID)

alter table Score add   constraint FK_Score_Course foreign key(CourseID) references Course(CourseID) 